-- 末端系统未完结
insert
    overwrite
    table jms_dm.dm_system_unfinished_summary_mid_dt
    partition
    (dt)
select t1.order_source_name
     , t1.final_sign_network_code
     , t1.final_plan_sign_date
     , t1.unfinished_count_two
     , t1.unfinished_fine
     , nvl(t2.is_shaidan, 0)        as is_shaidan
     , nvl(t2.is_delay, 0)          as is_delay
     , nvl(t2.is_new_open, 0)       as is_new_open
     , nvl(t2.is_special_report, 0) as is_special_report
     , t1.final_plan_sign_date      as dt
from (select order_source_name,
             final_sign_network_code,
             final_plan_sign_date,
             sum(unfinished_count_two) as unfinished_count_two,
             sum(unfinished_fine)      as unfinished_fine
      from (select if(
                  order_source_name not in ('桃花岛', '紫金山', '逍遥峰', '极地湾', '麦田圈', '苏宁', '有赞', '七星潭')
          , '其他', order_source_name)                                                       as order_source_name
                 , final_sign_network_code
                 , final_plan_sign_date
                 , sum(case when end_type in ('收派员派件', '其他') then 1 else 0 end)       as unfinished_count_two --收派员派件和其他
                 , sum(case when end_type in ('收派员派件', '其他') then 1 else 0 end) * 0.3 as unfinished_fine      --未完结罚款金额
            from jms_dm.dm_terminal_sign_detail_new_dt
            where dt between date_add('{{ execution_date | cst_ds }}', -30) and '{{ execution_date | cst_ds }}'
              and final_plan_sign_date = date_add('{{ execution_date | cst_ds }}', -6)
            group by if(order_source_name not in
                        ('桃花岛', '紫金山', '逍遥峰', '极地湾', '麦田圈', '苏宁', '有赞', '七星潭')
                , '其他', order_source_name)
                   , final_sign_network_code
                   , final_plan_sign_date
            union all
            select order_source_name,
                   scansitecode as final_sign_network_code,
                   dt           as final_plan_sign_date,
                   0,
                   0
            from jms_dm.dm_network_end_dispatch_diff_rate
            where dt = date_add('{{ execution_date | cst_ds }}', -6)
            group by dt
                   , order_source_name
                   , scansitecode) tmp
      group by order_source_name, final_sign_network_code, final_plan_sign_date) t1
         left join
     (select network_code
           , max(is_shaidan)        as is_shaidan
           , max(is_delay)          as is_delay
           , max(is_new_open)       as is_new_open
           , max(is_special_report) as is_special_report
      from jms_dim.dim_network_abnormal_type_union_dt
      where dt between date_add('{{ execution_date | cst_ds }}', -6) and '{{ execution_date | cst_ds }}'
      group by network_code) t2
     on t1.final_sign_network_code = t2.network_code
    distribute by dt
;
with sign_detail as (select if(
            order_source_name not in ('桃花岛', '紫金山', '逍遥峰', '极地湾', '麦田圈', '苏宁', '有赞', '七星潭')
    , '其他',
            order_source_name)                                                          as order_source_name           --订单来源名称                                                                      --订单来源名称
                          , final_sign_network_code                                                                    --末端网点所属加盟商编码
                          , final_plan_sign_date                                                                       --规划签收日期
                          , sum(1)                                                      as need_sign_count             --应签收
                          , sum(case when end_type = '完结' then 1 else 0 end)          as finished_count              --完结量
                          , sum(case when end_type <> '完结' then 1 else 0 end)         as unfinished_count            --未完结量
                          , sum(case when end_type = '第三方对接' then 1 else 0 end)    as unfinished_third_party      --第三方对接
                          , sum(case when end_type = '快件取出' then 1 else 0 end)      as unfinished_deliver_out      --快件取出
                          , sum(case when end_type = '收派员派件' then 1 else 0 end)    as unfinished_deliver          --收派员派件
                          , sum(case when end_type = '其他' then 1 else 0 end)          as unfinished_other            --其他
                          , sum(case when end_type = '问题件' then 1 else 0 end)        as unfinished_difficult        --问题件
                          , sum(case when end_duty_type = '网点出仓' then 1 else 0 end) as unfinished_duty_deliver     --未完结责任-网点出仓
                          , sum(case when end_duty_type = '网点到件' then 1 else 0 end) as unfinished_duty_end_network --未完结责任-网点到件
                          , sum(case when end_duty_type = '中心发件' then 1 else 0 end) as unfinished_duty_end_center  --未完结责任-中心发件
                          , 0                                                           as end_dispatch_count
                          , 0                                                           as end_dispatch_fine
                          , final_plan_sign_date                                        as dt
                     from jms_dm.dm_terminal_sign_detail_new_dt
                     where dt between date_add('{{ execution_date | cst_ds }}', -30) and '{{ execution_date | cst_ds }}'
                       and final_plan_sign_date between date_add('{{ execution_date | cst_ds }}', -14) and '{{ execution_date | cst_ds }}'
                     group by if(order_source_name not in
                                 ('桃花岛', '紫金山', '逍遥峰', '极地湾', '麦田圈', '苏宁', '有赞', '七星潭')
                         , '其他',
                                 order_source_name)   --订单来源名称
                            , final_sign_network_code --末端网点所属加盟商编码
                            --        , final_sign_network_name --最终签收派件网点名称+
                            , final_plan_sign_date --规划签收日期
),
     tmp_table as (select order_source_name
                        , final_sign_network_code
                        , final_plan_sign_date
                        , unfinished_count_two
                        , unfinished_fine
                        , is_shaidan
                        , is_delay
                        , is_new_open
                        , is_special_report
                        , dt
                   from jms_dm.dm_system_unfinished_summary_mid_dt
                   where dt <= date_add('{{ execution_date | cst_ds }}', -6)
                     and dt >= date_add('{{ execution_date | cst_ds }}', -14)),

     end_dispatch as (select t1.order_source_name                                            as order_source_name
                           , t1.scansitecode                                                 as final_sign_network_code
                           , t1.dt                                                           as final_plan_sign_date        --规划签收日期
                           , 0                                                               as need_sign_count             --应签收
                           , 0                                                               as finished_count              --完结量
                           , 0                                                               as unfinished_count            --未完结量
                           , 0                                                               as unfinished_third_party      --第三方对接
                           , 0                                                               as unfinished_deliver_out      --快件取出
                           , 0                                                               as unfinished_deliver          --收派员派件
                           , 0                                                               as unfinished_other            --其他
                           , 0                                                               as unfinished_difficult        --问题件
                           , 0                                                               as unfinished_duty_deliver     --未完结责任-网点出仓
                           , 0                                                               as unfinished_duty_end_network --未完结责任-网点到件
                           , 0                                                               as unfinished_duty_end_center  --未完结责任-中心发件
                           , nvl(sum(t1.store_in_total) - sum(t1.seven_store_over_total), 0) as end_dispatch_count          --	终端入库7日未完结
                           , nvl(sum(t1.store_in_total) - sum(t1.seven_store_over_total), 0) *
                             0.3                                                             as end_dispatch_fine           --	终端入库7日未完结罚款
                           , t1.dt
                      from jms_dm.dm_network_end_dispatch_diff_rate t1
                      where dt <= date_add('{{ execution_date | cst_ds }}', -6)
                        and dt >= date_add('{{ execution_date | cst_ds }}', -14)
                      group by order_source_name
                             , scansitecode
                             , dt),
     result_table as (select order_source_name
                           , final_sign_network_code
                           , max(final_plan_sign_date)                as final_plan_sign_date
                           , nvl(sum(need_sign_count), 0)             as need_sign_count
                           , nvl(sum(finished_count), 0)              as finished_count
                           , nvl(sum(unfinished_count), 0)            as unfinished_count
                           , nvl(sum(unfinished_third_party), 0)      as unfinished_third_party
                           , nvl(sum(unfinished_deliver_out), 0)      as unfinished_deliver_out
                           , nvl(sum(unfinished_deliver), 0)          as unfinished_deliver
                           , nvl(sum(unfinished_other), 0)            as unfinished_other
                           , nvl(sum(unfinished_difficult), 0)        as unfinished_difficult
                           , nvl(sum(unfinished_duty_deliver), 0)     as unfinished_duty_deliver
                           , nvl(sum(unfinished_duty_end_network), 0) as unfinished_duty_end_network
                           , nvl(sum(unfinished_duty_end_center), 0)  as unfinished_duty_end_center
                           , nvl(sum(end_dispatch_count), 0)          as end_dispatch_count
                           , nvl(sum(end_dispatch_fine), 0)           as end_dispatch_fine
                           , max(is_special_report)                   as is_special_report
                           , max(is_shaidan)                          as is_shaidan
                           , max(is_new_open)                         as is_new_open
                           , max(is_delay)                            as is_delay
                           , dt
                           , sum(unfinished_fine)                     as unfinished_fine      --未完结罚款金额
                           , sum(unfinished_count_two)                as unfinished_count_two --收派员派件和其他
                      from (select if(order_source_name not in
                                      ('桃花岛', '紫金山', '逍遥峰', '极地湾', '麦田圈', '苏宁', '有赞', '七星潭')
                          , '其他',
                                      order_source_name) as order_source_name    --订单来源名称                                                                      --订单来源名称
                                 , final_sign_network_code                       --末端网点所属加盟商编码
                                 , final_plan_sign_date                          --规划签收日期
                                 , need_sign_count                               --应签收
                                 , finished_count                                --完结量
                                 , unfinished_count                              --未完结量
                                 , unfinished_third_party                        --第三方对接
                                 , unfinished_deliver_out                        --快件取出
                                 , unfinished_deliver                            --收派员派件
                                 , unfinished_other                              --其他
                                 , unfinished_difficult                          --问题件
                                 , unfinished_duty_deliver                       --未完结责任-网点出仓
                                 , unfinished_duty_end_network                   --未完结责任-网点到件
                                 , unfinished_duty_end_center                    --未完结责任-中心发件
                                 , end_dispatch_count
                                 , end_dispatch_fine
                                 , dt
                                 , 0                     as is_special_report
                                 , 0                     as is_shaidan
                                 , 0                     as is_new_open
                                 , 0                     as is_delay
                                 , 0                     as unfinished_fine      --未完结罚款金额
                                 , 0                     as unfinished_count_two --收派员派件和其他
                            from sign_detail
                            union all
                            select order_source_name
                                 , final_sign_network_code
                                 , final_plan_sign_date        --规划签收日期
                                 , need_sign_count             --应签收
                                 , finished_count              --完结量
                                 , unfinished_count            --未完结量
                                 , unfinished_third_party      --第三方对接
                                 , unfinished_deliver_out      --快件取出
                                 , unfinished_deliver          --收派员派件
                                 , unfinished_other            --其他
                                 , unfinished_difficult        --问题件
                                 , unfinished_duty_deliver     --未完结责任-网点出仓
                                 , unfinished_duty_end_network --未完结责任-网点到件
                                 , unfinished_duty_end_center  --未完结责任-中心发件
                                 , end_dispatch_count          --	终端入库7日未完结
                                 , end_dispatch_fine           --	终端入库7日未完结罚款
                                 , dt
                                 , 0 as is_special_report
                                 , 0 as is_shaidan
                                 , 0 as is_new_open
                                 , 0 as is_delay
                                 , 0 as unfinished_fine        --未完结罚款金额
                                 , 0 as unfinished_count_two   --收派员派件和其他
                            from end_dispatch
                            where end_dispatch_count > 0
                            union all
                            select order_source_name                                                                         --订单来源名称
                                 , final_sign_network_code                                                                   --末端网点所属加盟商编码
                                 , final_plan_sign_date                                                                      --规划签收日期
                                 , 0                                                          as need_sign_count             --应签收
                                 , 0                                                          as finished_count              --完结量
                                 , 0                                                          as unfinished_count            --未完结量
                                 , 0                                                          as unfinished_third_party      --第三方对接
                                 , 0                                                          as unfinished_deliver_out      --快件取出
                                 , 0                                                          as unfinished_deliver          --收派员派件
                                 , 0                                                          as unfinished_other            --其他
                                 , 0                                                          as unfinished_difficult        --问题件
                                 , 0                                                          as unfinished_duty_deliver     --未完结责任-网点出仓
                                 , 0                                                          as unfinished_duty_end_network --未完结责任-网点到件
                                 , 0                                                          as unfinished_duty_end_center  --未完结责任-中心发件
                                 , 0                                                          as end_dispatch_count
                                 , 0                                                          as end_dispatch_fine
                                 , final_plan_sign_date                                       as dt
                                 , is_special_report
                                 , is_shaidan
                                 , is_new_open
                                 , is_delay
                                 , round(if(unfinished_fine >= 300, 300, unfinished_fine), 1) as unfinished_fine             --未完结罚款金额
                                 , unfinished_count_two                                                                      --收派员派件和其他
                            from tmp_table
                            -- where unfinished_count_two>0
                            ) a
                      group by order_source_name
                             , final_sign_network_code
                             , dt)
insert
overwrite
table
jms_dm.dm_system_unfinished_summary_dt
partition
(
dt
)
select t1.order_source_name                                                                            --订单来源名称
     , t3.manage_code                                                       as end_manage_region_code  --末端网点所属管理大区编码
     , t3.manage_name                                                       as end_manage_region_name  --末端网点所属管理大区名称
     , t3.agent_code                                                        as end_agent_code          --末端网点所属代理区编码
     , t3.agent_name                                                        as end_agent_name          --末端网点所属代理区名称
     , t3.fran_code                                                         as end_franchisee_code     --末端网点所属加盟商编码
     , t3.fran_name                                                         as end_franchisee_name     --末端网点所属加盟商名称
     , t1.final_sign_network_code                                                                      --末端网点所属加盟商编码
     , t3.name                                                              as final_sign_network_name --最终签收派件网点名称
     , t1.need_sign_count                                                                              --应签收
     , t1.finished_count                                                                               --完结量
     , t1.unfinished_count                                                                             --未完结量
     , t1.unfinished_third_party                                                                       --第三方对接
     , t1.unfinished_deliver_out                                                                       --快件取出
     , t1.unfinished_deliver                                                                           --收派员派件
     , t1.unfinished_other                                                                             --其他
     , t1.final_plan_sign_date                                                                         --规划签收日期
     , t1.unfinished_difficult                                                                         --问题件
     , t1.unfinished_duty_deliver                                                                      --未完结责任-网点出仓
     , t1.unfinished_duty_end_network                                                                  --未完结责任-网点到件
     , t1.unfinished_duty_end_center                                                                   --未完结责任-中心发件
     , t3.provider_id                                                       as end_provider_id         --末端网点所属省份id
     , t3.provider_desc                                                     as end_provider_name       --末端网点所属省份名称
     , t3.city_id                                                           as end_city_id             --末端网点所属城市id
     , t3.city_desc                                                         as end_city_name           --末端网点所属城市名称
     , t3.area_id                                                           as end_area_id             --末端网点所属区县id
     , t3.area_desc                                                         as end_area_name           --末端网点所属区县名称
     , t1.is_shaidan                                                                                   --是否筛单网点
     , t1.is_delay                                                                                     --是否顺延网点
     , t1.is_new_open                                                                                  --是否新开网点
     , t1.is_special_report                                                                            --是否特殊上报网点
     , t1.unfinished_fine                                                                              --未完结罚款金额
     , t1.end_dispatch_count                                                                           --终端入库7日未完结量
     , round(if(t1.end_dispatch_fine >= 300, 300, t1.end_dispatch_fine), 1) as end_dispatch_fine       --终端入库7日未完结罚款
     , t1.unfinished_count_two                                                                         --收派员派件和其他
     , t1.dt
from result_table t1
         join (select *
     from jms_dim.dim_network_whole_massage
         where is_enable=1
         and is_delete=1
         and network_type=6
         ) t3
      on t1.final_sign_network_code = t3.code
    distribute by t1.dt
;
